import pandas as pd

df=pd.read_excel('result3_2.xlsx')
df['省份']=df['正式登记证号'].apply(lambda x:x[0])
df1=df[df['省份']=='鄂']
df2=df[df['省份']=='桂']
df1=df1.groupby('分组标签')['省份'].count()
df2=df2.groupby('分组标签')['省份'].count()
df1=df1.reset_index()
df2=df2.reset_index()
df1=df1.sort_values(['省份'],ascending=False)
df1=df1.head(5)
df2=df2.sort_values(['省份'],ascending=False)
df2=df2.head(5)

df1.to_excel('湖北省前五组.xlsx',index=False)
df2.to_excel('广西省前五组.xlsx',index=False)
